package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class Pms_comment_replay25 {
	//插入
	@Test
	public void insert() {
		Connection conn=null;
		//准备数据库用户名，密码，url，driver
		String username="root";//用户名
		String password="root";//密码
		String driver="com.mysql.jdbc.Driver";//driver
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";//url
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);//这是属于反射的内容
			//获取连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="insert into Pms_comment_replay(id,comment_id,member_nick_name,member_icon,content,create_time,type)values(90,9,2,4,5,'2019-09-09',6)";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test 
	public void update() {
		Connection conn=null;
		//准备数据库用户名,密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中
			Class.forName(driver);
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="update Pms_comment_replay set member_nick_name=5 where id=2";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.staement
			st=conn.createStatement();
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
	}
	@Test
	public void delete() {
		Connection conn=null;
		//准备数据库用户名，密码，url，driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		//协议：//地址：端口号/数据库？连接属性设置
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机中，这是属于反射的内容
			Class.forName(driver);
			//获取数据库连接java.sql.Connection
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="delete from Pms_comment_replay where id=1";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"删除成功":"删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test
	public void selet() {
		Connection conn=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//2.加载驱动到虚拟机中
			Class.forName(driver);//这个是反射
			//DriverManager数据库连接
			conn=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="select * from Pms_comment_replay";
		Statement st=null;
		ResultSet rs=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			st=conn.createStatement();
			//执行sql命令
			rs=st.executeQuery(sql);
			//处理结果集
			while (rs.next()) {
				//通过columnLabel列名或者别名获取,
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getObject("comment_id"));
				System.out.println(rs.getObject("member_nick_name"));
				System.out.println(rs.getObject("member_icon"));
				System.out.println(rs.getObject("content"));
				System.out.println(rs.getObject("create_time"));
				System.out.println(rs.getObject("type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		
	}


}
